﻿using System;
using System.Collections.Generic;
using System.Text;
using AD.Model;
using System.Data.SqlClient;
using System.Data;

namespace AD.DAL
{
    public class AccountFlowDAL : DALBase
    {
        public static List<AccountFlowMod> QuerySum()
        {
            try
            {
                string sql = "select * from tab_account_flow_info,(select UserId,max(Commit_Date) Commit_Date from tab_account_flow_info group by UserId) Z where tab_account_flow_info.UserId=Z.UserId and tab_account_flow_info.Commit_Date=Z.Commit_Date";
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                List<AccountFlowMod> accountFlows = new List<AccountFlowMod>();
                foreach (DataRow row in dt.Rows)
                {
                    AccountFlowMod user = SetModel(row);
                    accountFlows.Add(user);
                }
                return accountFlows;
            }
            catch { return null; }
        }

        public static List<AccountFlowMod> QueryByPage(string filter, int pageSize, int index, out int MaxSize)
        {
            MaxSize = 0;
            try
            {
                MaxSize = Count();
                //string sql = "select top {0} * from (select top {1} * from users_info order by AutoID asc ) as a order by AutoID desc";
                string sql = "select top {0} * from tab_account_flow_info where (AutoID NOT IN (select top {1} AutoID from tab_account_flow_info order by AutoID)) order by AutoID";
                sql = string.Format(sql, pageSize, index * pageSize);
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                List<AccountFlowMod> accountFlows = new List<AccountFlowMod>();
                foreach (DataRow row in dt.Rows)
                {
                    AccountFlowMod user = SetModel(row);
                    accountFlows.Add(user);
                }
                return accountFlows;
            }
            catch { return null; }
        }
        public static int Count()
        {
            return QueryFilterToTable("").Rows.Count;
        }
        public static int Delete(string filter)
        {
            try
            {
                string sql = "delete tab_account_flow_info ";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                return ExecuteNonQuery(sql);
            }
            catch
            {
            }
            return 0;
        }
        
        public static int SumByUserId(int id)
        {
            return SumByUserId(id.ToString());
        }

        public static int SumByUserId(string id)
        {
            try
            {
                string sql = "Select Sum(BalancePoint) from tab_account_flow_info where OperationType=2 and UserId=" + id;
                SqlDataReader reader = ExecuteReader(sql);
                while (reader.Read())
                {
                    return reader.GetInt32(0);
                }
            }
            catch { }
            return 0;
        }

        public static IList<AccountFlowMod> QueryFilterToList(string filter)
        {
            try
            {
                DataTable dt = QueryFilterToTable(filter);
                if (dt == null || dt.Rows.Count <= 0) return null;
                List<AccountFlowMod> accountFlows = new List<AccountFlowMod>();
                foreach (DataRow row in dt.Rows)
                {
                    AccountFlowMod user = SetModel(row);
                    accountFlows.Add(user);
                }
                return accountFlows;
            }
            catch
            {
            }
            return null;
        }

        public static DataTable QueryFilterToTable(string filter)
        {
            try
            {
                string sql = "Select * from tab_account_flow_info ";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                sql += " order by Commit_Date desc ";
                return ExecuteDataTable(sql);
            }
            catch
            {
            }
            return null;
        }

        public static int GetDay(int userId,out int errcode)
        {
            errcode = 0;
            IList<AccountFlowMod> tmpmods = QueryFilterToList(" OperationType=2 and UserId=" + userId);
            if (tmpmods != null && tmpmods.Count > 0)
            {
                AccountFlowMod tmpmod = tmpmods[0];
                return GetDay(DateTime.Now, tmpmod.Commit_Date);
            }
            errcode = -1;
            return 0;
        }

        internal static int Insert(SqlCommand sqlCommand, SqlTransaction sqlTransaction, AccountFlowMod inviteFlow)
        {
            sqlCommand.Parameters.Clear();
            string sql = "insert into tab_account_flow_info(OperationName,UserId,OperationType,BalancePoint,Commit_Date,Remarks) values(@OperationName,@UserId,@OperationType,@BalancePoint,@Commit_Date,@Remarks)";
            SqlParameter[] sqlParams = GetSqlParameter(inviteFlow);
            PrepareCommand(sqlCommand, sqlTransaction, sql, sqlParams);
            int effect = sqlCommand.ExecuteNonQuery();
            if (effect >= 1)
                effect = UserDAL.UpdateBalancePoint(sqlCommand, sqlTransaction, inviteFlow.UserId, inviteFlow.BalancePoint);
            return effect;
        }

        public static int Insert(AccountFlowMod inviteFlow)
        {
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                if (SQL_CONNECT.State != ConnectionState.Open)
                    SQL_CONNECT.Open();
                SqlTransaction sqlTransaction = SQL_CONNECT.BeginTransaction();
                try
                {
                    int effect = Insert(sqlCommand, sqlTransaction, inviteFlow);
                    if (effect >= 1)
                        sqlTransaction.Commit();
                    else
                        sqlTransaction.Rollback();
                    return effect;
                }
                catch { sqlTransaction.Rollback(); }
            }
            catch { }
            return 0;
        }

        #region 模型

        static AccountFlowMod SetModel(DataRow row)
        {
            AccountFlowMod accountFlow = new AccountFlowMod();
            accountFlow.AutoID = int.Parse(row["AutoID"].ToString());

            if (row["BalancePoint"] != DBNull.Value)
                accountFlow.BalancePoint = int.Parse(row["BalancePoint"].ToString());

            if (row["Commit_Date"] != DBNull.Value)
                accountFlow.Commit_Date = DateTime.Parse(row["Commit_Date"].ToString());

            if (row["OperationType"] != DBNull.Value)
                accountFlow.OperationType = (OperationType)int.Parse(row["OperationType"].ToString());

            if (row["Remarks"] != DBNull.Value)
                accountFlow.Remarks = row["Remarks"].ToString();

            if (row["UserId"] != DBNull.Value)
                accountFlow.UserId = int.Parse(row["UserId"].ToString());

            if (row["OperationName"] != DBNull.Value)
                accountFlow.OperationName = row["OperationName"].ToString();
            return accountFlow;
        }

        static SqlParameter[] GetSqlParameter(AccountFlowMod accountFlow)
        {
            List<SqlParameter> param = new List<SqlParameter>();
            SqlParameter AutoID = new SqlParameter("@AutoID", SqlDbType.Int, 4, "AutoID");
            AutoID.Value = accountFlow.AutoID;
            param.Add(AutoID);

            SqlParameter BalancePoint = new SqlParameter("@BalancePoint", SqlDbType.Int, 4, "BalancePoint");
            BalancePoint.Value = accountFlow.BalancePoint;
            param.Add(BalancePoint);

            SqlParameter Commit_Date = new SqlParameter("@Commit_Date", SqlDbType.DateTime, 8, "Commit_Date");
            Commit_Date.Value = accountFlow.Commit_Date;
            param.Add(Commit_Date);

            SqlParameter OperationType = new SqlParameter("@OperationType", SqlDbType.Char, 1, "OperationType");
            OperationType.Value = (int)accountFlow.OperationType;
            param.Add(OperationType);

            SqlParameter Remarks = new SqlParameter("@Remarks", SqlDbType.VarChar, 300, "Remarks");
            Remarks.Value = accountFlow.Remarks;
            param.Add(Remarks);

            SqlParameter UserId = new SqlParameter("@UserId", SqlDbType.Int, 4, "UserId");
            UserId.Value = accountFlow.UserId;
            param.Add(UserId);

            SqlParameter OperationName = new SqlParameter("@OperationName", SqlDbType.VarChar, 50, "OperationName");
            OperationName.Value = accountFlow.OperationName;
            param.Add(OperationName);

            return param.ToArray();
        }

        #endregion
    }
}